﻿IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vrptClaimsSettled]'))
DROP VIEW [dbo].[vrptClaimsSettled]
go
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vrptClaimsGeneral]'))
DROP VIEW [dbo].[vrptClaimsGeneral]
go
IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[vrptClaimsWithReserve]'))
DROP VIEW [dbo].[vrptClaimsWithReserve]
go
create view [dbo].[vrptClaimsWithReserve] as
SELECT
	CLAIMS.S_N as SerialNumber
	, CLAIMS.ACTION_NO as ActionNumber
	, CLAIMS.CL_NAME as ClientName
	, CLAIMS.DEF_NAME as DefendantName
	, CLAIMS.BOD_INJ_RES as BodyInjuryAmount
	, CLAIMS.PROP_DAM_RES as PropertyDamageAmount
	, CLAIMS.COM_LXP_RES as LegalExpensesAmount
	, CLAIMS.OTH_CLM_RES as OtherExpensesAmount
	, CLAIMS.INIT_EST as InitialAmount
	, CLAIMS.EPAL_AMT as ConfirmationAmount
	, COURTS.DESCR as CourtDistrict
	, CLASS.DESCR as ClassificationDescription
	, CLAIMS.CLASS_CD as ClassificationCode
	, DB_NAME() as CurrentDatabaseName
FROM
	CLAIMS LEFT OUTER JOIN
		CLASS ON CLAIMS.CLASS_CD = CLASS.CODE LEFT OUTER JOIN
		COURTS ON CLAIMS.COURT_CD = COURTS.CODE
WHERE
	CLAIMS.STATUS = 'Ε'
go
create view [dbo].[vrptClaimsSettled] as
SELECT
	CLAIMS.S_N as SerialNumber
	, CLAIMS.ACTION_NO as ActionNumber
	, CLAIMS.CL_NAME as ClientName
	, CLAIMS.DEF_NAME as DefendantName
	, CLAIMS.BOD_INJ_PAY as BodyInjuryAmount
	, CLAIMS.PROP_DAM_PAY as PropertyDamageAmount
	, CLAIMS.LEG_EXP_PAY as LegalExpensesAmount
	, CLAIMS.INS_LXP_PAY as InsLegalExpensesAmount
	, CLAIMS.SET_REJ_DT as SettlementDate
	, CLAIMS.EPAL_AMT as ConfirmationAmount
	, COURTS.DESCR as CourtDistrict
	, CLASS.DESCR as ClassificationDescription
	, CLAIMS.CLASS_CD as ClassificationCode
	, DB_NAME() as CurrentDatabaseName
FROM
	CLAIMS LEFT OUTER JOIN
		CLASS ON CLAIMS.CLASS_CD = CLASS.CODE LEFT OUTER JOIN
		COURTS ON CLAIMS.COURT_CD = COURTS.CODE
WHERE
	CLAIMS.STATUS = 'Δ'
go
create view [dbo].[vrptClaimsGeneral] as
SELECT
	CLAIMS.S_N as SerialNumber
	, CLAIMS.CLASS_CD as ClassificationCode
	, CLAIMS.ACTION_NO as ActionNumber
	, CLAIMS.CLAIM_CL + '/' + CONVERT(VARCHAR(10), CLAIMS.CLAIM_NO) + '-' + CONVERT(VARCHAR(10), CLAIMS.CLAIM_YR) as ClaimNumber
	, CLAIMS.EPAL_NO as ConfirmationNumber
	, CLAIMS.CL_NAME as ClientName
	, CLAIMS.DEF_NAME as DefendantName
	, CLAIMS.INIT_EST as InitialEstimateAmount
	, CLAIMS.EPAL_AMT as ConfirmationAmount
	, CLAIMS.BOD_INJ_PAY + CLAIMS.PROP_DAM_PAY + CLAIMS.LEG_EXP_PAY + CLAIMS.INS_LXP_PAY as PaidFromFund
	, CLAIMS.BOD_INJ_LIQ + CLAIMS.PROP_DAM_LIQ + CLAIMS.OTH_EXP_LIQ + CLAIMS.CLM_LXP_LIQ + CLAIMS.NEM_LXP_LIQ as PaidFromLiq
	, CLAIMS.BOD_INJ_RES + CLAIMS.PROP_DAM_RES + CLAIMS.COM_LXP_RES + CLAIMS.OTH_CLM_RES as PendingAmount
	, DB_NAME() as CurrentDatabaseName
FROM
	CLAIMS
go
